package jsyf.oa.service.report.information;

import com.alibaba.fastjson.JSONObject;
import jsyf.com.util.FastJsonConvert;
import jsyf.oa.dao.report.information.PersonnelReportDao;
import jsyf.oa.facade.report.information.PersonnelReportFacade;
import jsyf.oa.service.report.reportCommon.ReportCommonMethodsAndConstant;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.Date;
import java.util.List;

/**
 * Created by Admin on 2018/4/5.
 */
@Service
@Transactional(rollbackFor = Exception.class)
@com.alibaba.dubbo.config.annotation.Service(protocol = {"dubbo"})
public class PersonnelReportService implements PersonnelReportFacade {
    @Autowired
    private PersonnelReportDao personnelReportDao;
    @Autowired
    private ReportCommonMethodsAndConstant common;

    @Override
    public List<JSONObject> getForCompanyByUserId(String jsonData) {
        JSONObject jsonParams = FastJsonConvert.convertJSONToObject(jsonData, JSONObject.class);
        String userId = jsonParams.getString("userId");
        String dateTime = jsonParams.getString("dateTime");
        StringBuilder sbSql = new StringBuilder();
        sbSql.append("SELECT depart.departId,depart.departName,userInfo.userId,userInfo.userName,userInfo.roleName,userInfo.jobState,userInfo.entryDate,t_sys_region.`NAME` as regionName\n" +
                ",CASE WHEN\n" +
                "FLOOR(DATEDIFF(left('" + personnelReportDao.getJdbcTemplate().queryForObject("select curdate();", Date.class) + "',10),userInfo.entryDate)/30) <> 0 \n" +
                "THEN \n" +
                "CONCAT(FLOOR(DATEDIFF(left('" + personnelReportDao.getJdbcTemplate().queryForObject("select curdate();", Date.class) + "',10),userInfo.entryDate)/30),'月',(DATEDIFF(left('" + personnelReportDao.getJdbcTemplate().queryForObject("select curdate();", Date.class) + "',10),userInfo.entryDate)%30),'天')\n" +
                "ELSE CONCAT((DATEDIFF(left('" + personnelReportDao.getJdbcTemplate().queryForObject("select curdate();", Date.class) + "',10),userInfo.entryDate)%30),'天')\n" +
                "END as entryTime\n" +
                "from (\n" +
                "\tSELECT ID as departId,`NAME` as departName, \n" +
                "\tCASE \n" +
                "\t\tWHEN PARENT_ID = '0' THEN SORT_NO *100\n" +
                "\t\tELSE (SELECT SORT_NO FROM t_sys_depart as departParent WHERE t_sys_depart.PARENT_ID = departParent.ID) *100 + SORT_NO + 10\n" +
                "\tEND as departSortNo\n" +
                "\tFROM t_sys_depart \n" +
                "\tWHERE FIND_IN_SET(ID,getChildrenDepart((\n" +
                "\t\tSELECT t_sys_role.DEPART_ID FROM t_sys_role WHERE t_sys_role.ID =(\n" +
                "\t\t\tSELECT t_sys_user.ROLE_ID FROM t_sys_user WHERE t_sys_user.ID = '" + userId + "'\n" +
                "\t)))) \n" +
                "\tAND DEL_FLAG = 0\n" +
                ") as depart\n" +
                "LEFT JOIN (\n" +
                "SELECT \n" +
                "t_sys_user.ID as userId,\n" +
                "t_sys_user.`NAME` as userName,\n" +
                "t_sys_role_name.`NAME` as roleName,\n" +
                "t_sys_depart.ID as departId,\n" +
                "t_sys_role.SORT_NO as roleSortNo,\n" +
                "t_sys_user.SORT_NO as userSortNo,\n" +
                "t_sys_user.JOB_STATE as jobState,\n" +
                "t_sys_user.ENTRY_DATE as entryDate,\n" +
                "CASE\n" +
                "\tWHEN t_sys_role_name.FRONT_FLAG = 1 AND AREA_TYPE = 1\n" +
                "\t\tTHEN \n" +
                "\t\t\t(SELECT t_sys_region.ID FROM t_sys_region \n" +
                "\t\t\t\t\t\t\t\tWHERE DEL_FLAG = 0 \n" +
                "\t\t\t\t\t\t\t\t\tAND PARENT_ID = '0' \n" +
                "\t\t\t\t\t\t\t\t\tAND ID in(SELECT REGION_ID FROM t_sys_user_2_region WHERE USER_ID = t_sys_user.ID) LIMIT 0,1) \n" +
                "\tWHEN t_sys_role_name.FRONT_FLAG = 1 AND AREA_TYPE = 0\n" +
                "\t\tTHEN\n" +
                "\t\t\t(SELECT t_sys_region.ID FROM t_sys_region \n" +
                "\t\t\t\t\t\t\t\t\tWHERE DEL_FLAG = 0 \n" +
                "\t\t\t\t\t\t\t\t\t\tAND PARENT_ID <> '0' \n" +
                "\t\t\t\t\t\t\t\t\t\tAND ID in(SELECT REGION_ID FROM t_sys_user_2_region WHERE USER_ID = t_sys_user.ID) LIMIT 0,1)\n" +
                "\tELSE '0'\n" +
                "END as regionId\n" +
                "FROM t_sys_user \n" +
                "LEFT JOIN t_sys_role ON t_sys_user.ROLE_ID = t_sys_role.ID\n" +
                "LEFT JOIN t_sys_depart ON t_sys_role.DEPART_ID = t_sys_depart.ID\n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID = t_sys_role_name.ID \n" +
                "LEFT JOIN t_sys_user_leave ON t_sys_user_leave.USER_ID=t_sys_user.ID\n" +
                "WHERE (t_sys_user.JOB_STATE <> 1 OR (t_sys_user.JOB_STATE <> 1 \n" +
                "AND t_sys_user_leave.LEAVE_DATE >" + "left('" + personnelReportDao.getJdbcTemplate().queryForObject("select curdate();", Date.class) + "',10)))\n" +
                ") as userInfo ON depart.departId =  userInfo.departId \n" +
                "LEFT JOIN t_sys_region ON t_sys_region.ID=userInfo.regionId\n" +
                "ORDER BY depart.departSortNo,userInfo.roleSortNo  , userInfo.userSortNo");
        return personnelReportDao.queryForJsonList(sbSql.toString());
    }

    @Override
    public List<JSONObject> getForCompanyByCompanyId(String jsonData) {
        JSONObject jsonParams = FastJsonConvert.convertJSONToObject(jsonData, JSONObject.class);
        String companyId = jsonParams.getString("companyId");
        StringBuilder sbSql = new StringBuilder();

        sbSql.append("SELECT obj.departName,obj.departId,obj.regionId,obj.regionName,obj.roleName,obj.userId,obj.userName,obj.jobState,obj.entryDate,obj.entryTime\n" +
                "FROM (\n" +
                "SELECT  depart.departName,t_sys_region.`NAME` as regionName, userInfo.*,flag.flag\n" +
                ",CASE WHEN\n" +
                "FLOOR(DATEDIFF(left('" + personnelReportDao.getJdbcTemplate().queryForObject("select curdate();", Date.class) + "',10),userInfo.entryDate)/30) <> 0 \n" +
                "THEN \n" +
                "CONCAT(FLOOR(DATEDIFF(left('" + personnelReportDao.getJdbcTemplate().queryForObject("select curdate();", Date.class) + "',10),userInfo.entryDate)/30),'月',(DATEDIFF(left('" + personnelReportDao.getJdbcTemplate().queryForObject("select curdate();", Date.class) + "',10),userInfo.entryDate)%30),'天')\n" +
                "ELSE CONCAT((DATEDIFF(left('" + personnelReportDao.getJdbcTemplate().queryForObject("select curdate();", Date.class) + "',10),userInfo.entryDate)%30),'天')\n" +
                "END as entryTime\n" +
                "from (\n" +
                "\tSELECT t_sys_depart.ID as departId,t_sys_depart.`NAME` as departName, \n" +
                "\tCASE \n" +
                "\t\tWHEN t_sys_depart.PARENT_ID = '0' THEN t_sys_depart.SORT_NO *100\n" +
                "\t\tELSE (SELECT departParent.SORT_NO FROM t_sys_depart as departParent WHERE t_sys_depart.PARENT_ID = departParent.ID) *100 + t_sys_depart.SORT_NO + 10\n" +
                "\tEND as departSortNo\n" +
                "\tFROM t_sys_depart \n" +
                "\tLEFT JOIN t_sys_role ON t_sys_role.DEPART_ID = t_sys_depart.ID\n" +
                "\tLEFT JOIN t_sys_user ON t_sys_user.ROLE_ID = t_sys_role.ID\n" +
                "\tLEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID = t_sys_role_name.ID \n" +
                "\tWHERE t_sys_depart.COMPANY_ID = '" + companyId + "'\n" +
                "\tAND t_sys_depart.DEL_FLAG = 0\n" +
                "GROUP BY t_sys_depart.`NAME`\n" +
                ") as depart\n" +
                "LEFT JOIN (\n" +
                "SELECT \n" +
                "v_sys_part_time_for_report.userId ,\n" +
                "v_sys_part_time_for_report.roleId ,\n" +
                "v_sys_part_time_for_report.roleNameId ,\n" +
                "v_sys_part_time_for_report.`userName` ,\n" +
                "t_sys_role_name.`NAME` as roleName,\n" +
                "v_sys_part_time_for_report.departId ,\n" +
                "v_sys_part_time_for_report.roleSortNo ,\n" +
                "v_sys_part_time_for_report.userSortNo ,\n" +
                "v_sys_part_time_for_report.jobState ,\n" +
                "v_sys_part_time_for_report.entryDate ,\n" +
                "CASE\n" +
                "\tWHEN t_sys_role_name.FRONT_FLAG = 1 AND AREA_TYPE = 1\n" +
                "\t\tTHEN \n" +
                "\t\t\t(SELECT t_sys_region.ID FROM t_sys_region \n" +
                "\t\t\t\t\t\t\t\tWHERE DEL_FLAG = 0 \n" +
                "\t\t\t\t\t\t\t\t\tAND PARENT_ID = '0' \n" +
                "\t\t\t\t\t\t\t\t\tAND ID in(SELECT REGION_ID FROM t_sys_user_2_region WHERE USER_ID = v_sys_part_time_for_report.userID) LIMIT 0,1) \n" +
                "\tWHEN t_sys_role_name.FRONT_FLAG = 1 AND AREA_TYPE = 0\n" +
                "\t\tTHEN\n" +
                "\t\t\t(SELECT t_sys_region.ID FROM t_sys_region \n" +
                "\t\t\t\t\t\t\t\t\tWHERE DEL_FLAG = 0 \n" +
                "\t\t\t\t\t\t\t\t\t\tAND PARENT_ID <> '0' \n" +
                "\t\t\t\t\t\t\t\t\t\tAND ID in(SELECT REGION_ID FROM t_sys_user_2_region WHERE USER_ID = v_sys_part_time_for_report.userID) LIMIT 0,1)\n" +
                "\tELSE '0'\n" +
                "END as regionId\n" +
                "FROM v_sys_part_time_for_report\n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role_name.ID = v_sys_part_time_for_report.roleNameId \n" +
                "LEFT JOIN t_sys_user_leave ON t_sys_user_leave.USER_ID=v_sys_part_time_for_report.userID\n" +
                "WHERE 1=1\n" +
                " AND t_sys_role_name.DEL_FLAG =0\n" +
                " AND(v_sys_part_time_for_report.jobState <> 1 OR (v_sys_part_time_for_report.jobState <> 1 \n" +
                "AND t_sys_user_leave.LEAVE_DATE >left('" + personnelReportDao.getJdbcTemplate().queryForObject("select curdate();", Date.class) + "',10))or jobState is null )\n" +
                ") as userInfo ON depart.departId =  userInfo.departId\n" +
                "LEFT JOIN (SELECT\n" +
                "v_sys_part_time_for_report.roleNameId,1 as flag\n" +
                "FROM\n" +
                "\tv_sys_part_time_for_report\n" +
                "WHERE \n" +
                "\tv_sys_part_time_for_report.companyId = '"+companyId+"'\n" +
                "GROUP BY\n" +
                "\tv_sys_part_time_for_report.roleNameId\n" +
                "HAVING count(v_sys_part_time_for_report.roleNameId)=1 ) as flag ON flag.roleNameId=userInfo.roleNameId\n" +
                "LEFT JOIN t_sys_region ON t_sys_region.ID=userInfo.regionId\n" +
                " ORDER BY depart.departSortNo,userInfo.roleSortNo  , userInfo.userSortNo) as obj\n" +
                "WHERE 1=1 \n" +
                "AND (obj.flag=1 OR obj.userId is not null)");
        return personnelReportDao.queryForJsonList(sbSql.toString());
    }

    @Override
    public List<JSONObject> getSumUpForCompanyByUserId(String jsonData) {
        JSONObject jsonParams = FastJsonConvert.convertJSONToObject(jsonData, JSONObject.class);
        String userId = jsonParams.getString("userId");
        StringBuilder sbSql = new StringBuilder();
        sbSql.append("SELECT \n" +
                "  sysUser.roleName,  \n" +
                "  SUM(sysUser.targetNumber) as targetNum,\n" +
                "  sysRole.nowNumber as nowNum,\n" +
                "  (SUM(sysUser.targetNumber) - sysRole.nowNumber) as vacancyNum\n" +
                "FROM (\n" +
                " SELECT t_sys_depart.`NAME` AS departName,t_sys_role_name.ID as roleNameId,t_sys_role.ID as roleId,`t_sys_role_name`.`NAME` AS roleName,t_sys_role.TARGET_NUMBER as targetNumber\n" +
                " FROM `t_sys_user` \n" +
                " LEFT JOIN `t_sys_user_leave` ON `t_sys_user`.`ID` = `t_sys_user_leave`.`USER_ID` \n" +
                " LEFT JOIN `t_sys_role` ON `t_sys_user`.`ROLE_ID` = `t_sys_role`.`ID` \n" +
                " LEFT JOIN `t_sys_role_name` ON `t_sys_role`.`ROLE_NAME_ID` = `t_sys_role_name`.`ID` \n" +
                " LEFT JOIN t_sys_depart ON t_sys_role.DEPART_ID = t_sys_depart.ID\n" +
                " WHERE 1 = 1 \n" +
                " AND ( t_sys_user.ID in\n" +
                "        (SELECT USER_ID FROM t_sys_user_2_region WHERE REGION_ID in(\n" +
                "           SELECT REGION_ID FROM t_sys_user_2_region where USER_ID = '" + userId + "'))\n" +
                " AND t_sys_user.ROLE_ID in(\n" +
                "\t     SELECT ID FROM t_sys_role where ROLE_NAME_ID in(\n" +
                "\t\t      SELECT ID from t_sys_role_name where FRONT_FLAG = 1))) \n" +
                " AND (t_sys_depart.id = (select depart_id from t_sys_role where id = \n" +
                "      (select role_id from t_sys_user where id = '" + userId + "'))\n" +
                "\tOR t_sys_depart.parent_id = (select depart_id from t_sys_role where id = \n" +
                "      (select role_id from t_sys_user where id = '" + userId + "')))\n" +
                "GROUP BY t_sys_role_name.`NAME`,t_sys_depart.`NAME`\n" +
                "ORDER BY t_sys_depart.`NAME`,t_sys_role_name.`NAME`) as sysUser\n" +
                "\n" +
                "LEFT JOIN(\n" +
                "SELECT t_sys_role_name.ID as roleNameId,t_sys_role_name.`NAME` as roleName,COUNT(t_sys_role_name.`NAME`) as nowNumber\n" +
                "FROM t_sys_user \n" +
                "LEFT JOIN `t_sys_role` ON `t_sys_user`.`ROLE_ID` = `t_sys_role`.`ID` \n" +
                "LEFT JOIN t_sys_depart ON t_sys_role.DEPART_ID = t_sys_depart.ID\n" +
                "LEFT JOIN `t_sys_role_name` ON `t_sys_role`.`ROLE_NAME_ID` = `t_sys_role_name`.`ID` \n" +
                "LEFT JOIN t_sys_user_leave ON t_sys_user_leave.USER_ID = t_sys_user.ID\n" +
                "WHERE 1 = 1 \n" +
                "AND JOB_STATE <> 1 \n" +
                "AND (t_sys_user.ID in(SELECT USER_ID FROM t_sys_user_2_region WHERE REGION_ID in(\n" +
                "  SELECT REGION_ID FROM t_sys_user_2_region where USER_ID = '" + userId + "')))\n" +
                "AND (t_sys_depart.id = (select depart_id from t_sys_role where id = (select role_id from t_sys_user where id = '" + userId + "'))\n" +
                "\tor t_sys_depart.parent_id = (select depart_id from t_sys_role where id = (select role_id from t_sys_user where id = '" + userId + "')))\n" +
                "AND t_sys_user.ROLE_ID in(\n" +
                "\tSELECT ID FROM t_sys_role where ROLE_NAME_ID in(\n" +
                "\t\tSELECT ID from t_sys_role_name where FRONT_FLAG = 1))\n" +
                "GROUP BY t_sys_role_name.`NAME`)  as sysRole\n" +
                "ON sysUser.roleNameId = sysRole.roleNameId\n" +
                "GROUP BY sysUser.roleName");
        return personnelReportDao.queryForJsonList(sbSql.toString());
    }

    @Override
    public List<JSONObject> getSumUpForCompanyLeaveByUserId(String jsonData) {
        JSONObject jsonParams = FastJsonConvert.convertJSONToObject(jsonData, JSONObject.class);
        String userId = jsonParams.getString("userId");
        String dateTime = jsonParams.getString("dateTime");
        StringBuilder sbSql = new StringBuilder();
        sbSql.append("SELECT \n" +
                " COUNT(t_sys_user_leave.LEAVE_DATE) as leaveCount\n" +
                " FROM `t_sys_user` \n" +
                " LEFT JOIN `t_sys_role` ON `t_sys_user`.`ROLE_ID` = `t_sys_role`.`ID` \n" +
                " LEFT JOIN `t_sys_role_name` ON `t_sys_role`.`ROLE_NAME_ID` = `t_sys_role_name`.`ID` \n" +
                " LEFT JOIN t_sys_depart ON t_sys_role.DEPART_ID = t_sys_depart.ID\n" +
                " LEFT JOIN t_sys_user_leave ON t_sys_user.ID = t_sys_user_leave.USER_ID\n" +
                " WHERE 1 = 1 \n" +
                " AND ( t_sys_user.ID in\n" +
                "        (SELECT USER_ID FROM t_sys_user_2_region WHERE REGION_ID in(\n" +
                "           SELECT REGION_ID FROM t_sys_user_2_region where USER_ID = '" + userId + "'))\n" +
                " AND t_sys_user.ROLE_ID in(\n" +
                "\t     SELECT ID FROM t_sys_role where ROLE_NAME_ID in(\n" +
                "\t\t      SELECT ID from t_sys_role_name where FRONT_FLAG = 1))) \n" +
                " AND (t_sys_depart.id = (select depart_id from t_sys_role where id = \n" +
                "      (select role_id from t_sys_user where id = '" + userId + "'))\n" +
                "\tOR t_sys_depart.parent_id = (select depart_id from t_sys_role where id = \n" +
                "      (select role_id from t_sys_user where id = '" + userId + "')))\n" +
                "AND t_sys_user_leave.LEAVE_DATE = '" + dateTime + "'\n" +
                "\n");
        return personnelReportDao.queryForJsonList(sbSql.toString());
    }

    @Override
    public List<JSONObject> getSumUpForCompanyEntryByUserId(String jsonData) {
        JSONObject jsonParams = FastJsonConvert.convertJSONToObject(jsonData, JSONObject.class);
        String userId = jsonParams.getString("userId");
        String dateTime = jsonParams.getString("dateTime");
        StringBuilder sbSql = new StringBuilder();
        sbSql.append("SELECT \n" +
                " COUNT(t_sys_user.ENTRY_DATE) as entryCount\n" +
                " FROM `t_sys_user` \n" +
                " LEFT JOIN `t_sys_role` ON `t_sys_user`.`ROLE_ID` = `t_sys_role`.`ID` \n" +
                " LEFT JOIN `t_sys_role_name` ON `t_sys_role`.`ROLE_NAME_ID` = `t_sys_role_name`.`ID` \n" +
                " LEFT JOIN t_sys_depart ON t_sys_role.DEPART_ID = t_sys_depart.ID\n" +
                " WHERE 1 = 1 \n" +
                " AND ( t_sys_user.ID in\n" +
                "        (SELECT USER_ID FROM t_sys_user_2_region WHERE REGION_ID in(\n" +
                "           SELECT REGION_ID FROM t_sys_user_2_region where USER_ID = '" + userId + "'))\n" +
                " AND t_sys_user.ROLE_ID in(\n" +
                "\t     SELECT ID FROM t_sys_role where ROLE_NAME_ID in(\n" +
                "\t\t      SELECT ID from t_sys_role_name where FRONT_FLAG = 1))) \n" +
                " AND (t_sys_depart.id = (select depart_id from t_sys_role where id = \n" +
                "      (select role_id from t_sys_user where id = '" + userId + "'))\n" +
                "\tOR t_sys_depart.parent_id = (select depart_id from t_sys_role where id = \n" +
                "      (select role_id from t_sys_user where id = '" + userId + "')))\n" +
                "AND t_sys_user.ENTRY_DATE = '" + dateTime + "'");
        return personnelReportDao.queryForJsonList(sbSql.toString());
    }

    @Override
    public List<JSONObject> getForGroupByUserId(String jsonData) {
        JSONObject jsonParams = FastJsonConvert.convertJSONToObject(jsonData, JSONObject.class);
        String userId = jsonParams.getString("userId");
        String compnayIds = common.getCompanysIdExceptByUserId(userId);
        StringBuilder sbSql = new StringBuilder();
        sbSql.append("SELECT company.companyId,company.companyName,\n" +
                "\tIFNULL(bigRegionManagerTarget.targetNumber,0) as bigRegionManagerTargetNum,\n" +
                "\tIFNULL(bigRegionManagerNow.num,0) as bigRegionManagerNowNum,\n" +
                "IFNULL(bigRegionManagerTarget.targetNumber,0) - IFNULL(bigRegionManagerNow.num,0) \n" +
                "as bigRegionManagerVacancyNum,\n" +
                "IFNULL(regionManagerTarget.targetNumber,0) as regionManagerTargetNum,\n" +
                "\tIFNULL(regionManagerNow.num,0) as regionManagerNowNum,\n" +
                "IFNULL(regionManagerTarget.targetNumber,0) - IFNULL(regionManagerNow.num,0) \n" +
                " as regionManagerVacancyNum ,\n" +
                "IFNULL(bigCustomerManageTarget.targetNumber,0) as bigCustomerManageTargetNum,\n" +
                "\tIFNULL(bigCustomerManageNow.num,0) as bigCustomerManageNowNum,\n" +
                "IFNULL(bigCustomerManageTarget.targetNumber,0) - IFNULL(bigCustomerManageNow.num,0) \n" +
                "as bigCustomerManageVacancyNum,\n" +
                "IFNULL(bigCustomerDirectorTarget.targetNumber,0) as bigCustomerDirectorTargetNum,\n" +
                "\tIFNULL(bigCustomerDirectorNow.num,0) as bigCustomerDirectorNowNum,\n" +
                "IFNULL(bigCustomerDirectorTarget.targetNumber,0) - IFNULL(bigCustomerDirectorNow.num,0) \n" +
                "as bigCustomerDirectorVacancyNum\n" +
                "FROM(\n" +
                "\tSELECT ID as companyId,`NAME` as companyName,\n" +
                "\tCASE \t\n" +
                "\t\tWHEN PARENT_ID = '0' THEN SORT_NO * 100\n" +
                "\t\tELSE (SELECT SORT_NO FROM t_sys_company companyParent WHERE t_sys_company.PARENT_ID = companyParent.ID)*100 + SORT_NO + 10\n" +
                "\tEND as companySortNo\n" +
                "\tFROM t_sys_company \n" +
                "\tWHERE t_sys_company.id in (" + compnayIds + ") " +
                "\tAND DEL_FLAG = 0 \n" +
                ") as company \n" +
                "LEFT JOIN(\n" +
                "\tSELECT t_sys_role_name.`NAME` as roleName,t_sys_company.ID as companyId,SUM(TARGET_NUMBER) as targetNumber from t_sys_role\n" +
                "\tLEFT JOIN t_sys_depart ON t_sys_role.DEPART_ID = t_sys_depart.ID\n" +
                "\tLEFT JOIN t_sys_company ON t_sys_depart.COMPANY_ID = t_sys_company.ID\n" +
                "\tLEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID = t_sys_role_name.ID\n" +
                "\tWHERE t_sys_company.DEL_FLAG = 0 and t_sys_depart.DEL_FLAG = 0 and t_sys_role.DEL_FLAG = 0\n" +
                "\tAND t_sys_role_name.`NAME` in('大区经理')\n" +
                "\tGROUP BY t_sys_company.ID\n" +
                ") as bigRegionManagerTarget ON company.companyId = bigRegionManagerTarget.companyId\n" +
                "LEFT JOIN(\n" +
                "\tSELECT t_sys_role_name.`NAME` as roleName,t_sys_company.ID as companyId,SUM(TARGET_NUMBER) as targetNumber from t_sys_role\n" +
                "\tLEFT JOIN t_sys_depart ON t_sys_role.DEPART_ID = t_sys_depart.ID\n" +
                "\tLEFT JOIN t_sys_company ON t_sys_depart.COMPANY_ID = t_sys_company.ID\n" +
                "\tLEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID = t_sys_role_name.ID\n" +
                "\tWHERE t_sys_company.DEL_FLAG = 0 and t_sys_depart.DEL_FLAG = 0 and t_sys_role.DEL_FLAG = 0\n" +
                "\tAND t_sys_role_name.`NAME` in('区域经理')\n" +
                "\tGROUP BY t_sys_company.ID\n" +
                ") as regionManagerTarget ON company.companyId = regionManagerTarget.companyId\n" +
                "LEFT JOIN(\n" +
                "\tSELECT t_sys_role_name.`NAME` as roleName,t_sys_company.ID as companyId,SUM(TARGET_NUMBER) as targetNumber from t_sys_role\n" +
                "\tLEFT JOIN t_sys_depart ON t_sys_role.DEPART_ID = t_sys_depart.ID\n" +
                "\tLEFT JOIN t_sys_company ON t_sys_depart.COMPANY_ID = t_sys_company.ID\n" +
                "\tLEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID = t_sys_role_name.ID\n" +
                "\tWHERE t_sys_company.DEL_FLAG = 0 and t_sys_depart.DEL_FLAG = 0 and t_sys_role.DEL_FLAG = 0\n" +
                "\tAND t_sys_role_name.`NAME` in('大客户经理')\n" +
                "\tGROUP BY t_sys_company.ID\n" +
                ") as bigCustomerManageTarget ON company.companyId = bigCustomerManageTarget.companyId\n" +
                "LEFT JOIN(\n" +
                "\tSELECT t_sys_role_name.`NAME` as roleName,t_sys_company.ID as companyId,SUM(TARGET_NUMBER) as targetNumber from t_sys_role\n" +
                "\tLEFT JOIN t_sys_depart ON t_sys_role.DEPART_ID = t_sys_depart.ID\n" +
                "\tLEFT JOIN t_sys_company ON t_sys_depart.COMPANY_ID = t_sys_company.ID\n" +
                "\tLEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID = t_sys_role_name.ID\n" +
                "\tWHERE t_sys_company.DEL_FLAG = 0 and t_sys_depart.DEL_FLAG = 0 and t_sys_role.DEL_FLAG = 0\n" +
                "\tAND t_sys_role_name.`NAME` in('大客户总监')\n" +
                "\tGROUP BY t_sys_company.ID\n" +
                ") as bigCustomerDirectorTarget ON company.companyId = bigCustomerDirectorTarget.companyId\n" +
                "LEFT JOIN (\n" +
                "\tSELECT t_sys_company.ID as companyId,COUNT(1) as num FROM t_sys_user\n" +
                "\tLEFT JOIN t_sys_role ON t_sys_user.ROLE_ID = t_sys_role.ID\n" +
                "\tLEFT JOIN t_sys_depart ON t_sys_role.DEPART_ID = t_sys_depart.ID\n" +
                "\tLEFT JOIN t_sys_company ON t_sys_depart.COMPANY_ID = t_sys_company.ID\n" +
                "\tLEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID = t_sys_role_name.ID\n" +
                "\tWHERE t_sys_user.JOB_STATE <> 1 AND t_sys_role.DEL_FLAG = 0 and t_sys_depart.DEL_FLAG = 0 and t_sys_company.DEL_FLAG = 0\n" +
                "\tAND t_sys_role_name.`NAME` IN ('大区经理')\n" +
                "\tGROUP BY t_sys_company.ID\n" +
                ") as bigRegionManagerNow ON company.companyId = bigRegionManagerNow.companyId\n" +
                "LEFT JOIN (\n" +
                "\tSELECT t_sys_company.ID as companyId,COUNT(1) as num FROM t_sys_user\n" +
                "\tLEFT JOIN t_sys_role ON t_sys_user.ROLE_ID = t_sys_role.ID\n" +
                "\tLEFT JOIN t_sys_depart ON t_sys_role.DEPART_ID = t_sys_depart.ID\n" +
                "\tLEFT JOIN t_sys_company ON t_sys_depart.COMPANY_ID = t_sys_company.ID\n" +
                "\tLEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID = t_sys_role_name.ID\n" +
                "\tWHERE t_sys_user.JOB_STATE <> 1 AND t_sys_role.DEL_FLAG = 0 and t_sys_depart.DEL_FLAG = 0 and t_sys_company.DEL_FLAG = 0\n" +
                "\tAND t_sys_role_name.`NAME` IN ('区域经理')\n" +
                "\tGROUP BY t_sys_company.ID\n" +
                ") as regionManagerNow ON company.companyId = regionManagerNow.companyId\n" +
                "LEFT JOIN (\n" +
                "\tSELECT t_sys_company.ID as companyId,COUNT(1) as num FROM t_sys_user\n" +
                "\tLEFT JOIN t_sys_role ON t_sys_user.ROLE_ID = t_sys_role.ID\n" +
                "\tLEFT JOIN t_sys_depart ON t_sys_role.DEPART_ID = t_sys_depart.ID\n" +
                "\tLEFT JOIN t_sys_company ON t_sys_depart.COMPANY_ID = t_sys_company.ID\n" +
                "\tLEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID = t_sys_role_name.ID\n" +
                "\tWHERE t_sys_user.JOB_STATE <> 1 AND t_sys_role.DEL_FLAG = 0 and t_sys_depart.DEL_FLAG = 0 and t_sys_company.DEL_FLAG = 0\n" +
                "\tAND t_sys_role_name.`NAME` IN ('大客户经理')\n" +
                "\tGROUP BY t_sys_company.ID\n" +
                ") as bigCustomerManageNow ON company.companyId = bigCustomerManageNow.companyId\n" +
                "LEFT JOIN (\n" +
                "\tSELECT t_sys_company.ID as companyId,COUNT(1) as num FROM t_sys_user\n" +
                "\tLEFT JOIN t_sys_role ON t_sys_user.ROLE_ID = t_sys_role.ID\n" +
                "\tLEFT JOIN t_sys_depart ON t_sys_role.DEPART_ID = t_sys_depart.ID\n" +
                "\tLEFT JOIN t_sys_company ON t_sys_depart.COMPANY_ID = t_sys_company.ID\n" +
                "\tLEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID = t_sys_role_name.ID\n" +
                "\tWHERE t_sys_user.JOB_STATE <> 1 AND t_sys_role.DEL_FLAG = 0 and t_sys_depart.DEL_FLAG = 0 and t_sys_company.DEL_FLAG = 0\n" +
                "\tAND t_sys_role_name.`NAME` IN ('大客户总监')\n" +
                "\tGROUP BY t_sys_company.ID\n" +
                ") as bigCustomerDirectorNow ON company.companyId = bigCustomerDirectorNow.companyId\n" +
                "ORDER BY company.companySortNo");
        return personnelReportDao.queryForJsonList(sbSql.toString());
    }

    @Override
    public List<JSONObject> getSumUpForGroupByUserId(String jsonData) {
        JSONObject jsonParams = FastJsonConvert.convertJSONToObject(jsonData, JSONObject.class);
        String userId = jsonParams.getString("userId");
        StringBuilder sbSql = new StringBuilder();
        sbSql.append("SELECT sysCompany.roleName,SUM(sysCompany.targetNumber) as targetNum,SUM(sysCompany.nowNumber) as nowNum,SUM(sysCompany.vacancy) as vacancyNum\n" +
                "FROM(\n" +
                "SELECT \n" +
                " company.ID AS companyId,\n" +
                " company.`NAME` AS companyName,\n" +
                " roleName.`NAME` AS roleName,\n" +
                " SUM(role.TARGET_NUMBER) AS targetNumber,\n" +
                " (select COUNT(1) FROM v_sys_user WHERE companyId=company.ID AND roleNameId=roleName.ID and delFlag=0 AND jobState!=1) AS nowNumber,\n" +
                " CASE WHEN (SELECT SUM(role.TARGET_NUMBER)-\n" +
                "(select COUNT(1) FROM v_sys_user WHERE companyId=company.ID AND roleNameId=roleName.ID and delFlag=0 AND jobState!=1))> 0 \n" +
                "THEN (SELECT SUM(role.TARGET_NUMBER)-(select COUNT(1) FROM v_sys_user WHERE companyId=company.ID AND roleNameId=roleName.ID \n" +
                "and delFlag=0 AND jobState!=1)) ELSE 0 END as vacancy \n" +
                "FROM   t_sys_company AS company\n" +
                "LEFT JOIN t_sys_depart AS depart ON company.ID=depart.COMPANY_ID\n" +
                "LEFT JOIN t_sys_role AS role ON depart.ID = role.DEPART_ID \n" +
                "LEFT JOIN t_sys_role_name AS roleName ON role.ROLE_NAME_ID = roleName.ID \n" +
                "WHERE\n" +
                " 1=1\n" +
                "AND (company.ID='201712161613432EAF27DBCCB7D0E6CA' OR company.PARENT_ID='201712161613432EAF27DBCCB7D0E6CA')\n" +
                "AND roleName.FRONT_FLAG=1\n" +
                "AND company.DEL_FLAG=0\n" +
                "AND depart.DEL_FLAG=0\n" +
                "AND role.DEL_FLAG=0 \n" +
                "AND roleName.DEL_FLAG=0 \n" +
                "GROUP BY company.ID,roleName.ID) as sysCompany\n" +
                "GROUP BY sysCompany.roleName\n");
        return personnelReportDao.queryForJsonList(sbSql.toString());
    }

    @Override
    public List<JSONObject> getSumUpForGroupEntryByUserId(String jsonData) {
        JSONObject jsonParams = FastJsonConvert.convertJSONToObject(jsonData, JSONObject.class);
        String userId = jsonParams.getString("userId");
        String dateTime = jsonParams.getString("dateTime");
        StringBuilder sbSql = new StringBuilder();
        sbSql.append("SELECT \n" +
                "COUNT(1) as entryNum\n" +
                "from t_sys_user\n" +
                "WHERE 1 = 1\n" +
                "AND t_sys_user.ENTRY_DATE = '" + dateTime + "'");
        return personnelReportDao.queryForJsonList(sbSql.toString());
    }

    @Override
    public List<JSONObject> getSumUpForGroupLeaveByUserId(String jsonData) {
        JSONObject jsonParams = FastJsonConvert.convertJSONToObject(jsonData, JSONObject.class);
        String userId = jsonParams.getString("userId");
        String dateTime = jsonParams.getString("dateTime");
        StringBuilder sbSql = new StringBuilder();
        sbSql.append("SELECT \n" +
                "COUNT(1) as leaveNum\n" +
                "from t_sys_user_leave\n" +
                "WHERE 1 = 1\n" +
                "AND t_sys_user_leave.LEAVE_DATE = '" + dateTime + "'");
        return personnelReportDao.queryForJsonList(sbSql.toString());
    }


}
